package user.Model.dao;

import user.Model.entity.book;
import user.Model.entity.record;
import user.utils.DBUitls;

import java.sql.*;
import java.util.ArrayList;

public class dao {
    //修改个人信息
    public static int changeUserInformation(int id,String username,String password,String postbox,String phoneNumber,String name,String direction,String phase) throws Exception{
        Connection connection = DBUitls.getConnection();
        String sql = "update user set username = '" + username +"' ,password = '" + password +"' , postbox = '" + postbox +"', phoneNumber = '" + phoneNumber +"',name = '"+ name +"',direction = '" + direction +"',phase = '"+ phase +"' where id = '"+ id +"'" ;
        Statement statement = connection.prepareStatement(sql);
        int count = statement.executeUpdate(sql);
        statement.close();
        return count;
    }
    //得到该用户的id用来查询
    public static int getId(String username)throws Exception{
        Connection connection = DBUitls.getConnection();
        String sql = "select id from user where username = '" + username +"'" ;
        Statement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        int id = 0;
        while(resultSet.next()){
            id = (int)resultSet.getInt(1);
        }
        resultSet.close();
        statement.close();
        return id;
    }
    //获取各类型的书籍
    public static ArrayList<book> searchBookKind(String kind) throws Exception {
        Connection connection = DBUitls.getConnection();
        String sql = "select * from book where kind = '"+ kind +"'";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        ArrayList<book> books = new ArrayList<>();
        while(resultSet.next()){
            //查询并创建对象
            String bookName = resultSet.getString(2);
            String writer = resultSet.getString(3);
            String number = resultSet.getString(5);
            String publish = resultSet.getString(6);
            String bookIntroduction = resultSet.getString(7);
            String writerIntroduction = resultSet.getString(8);
            String status = resultSet.getString(9);
            String borrowNumber = resultSet.getString(10);
            String picture = resultSet.getString(11);
            String borrower = resultSet.getString(12);
            book Book = new book(bookName,writer,kind,number,publish,bookIntroduction,writerIntroduction,status,borrowNumber,picture,borrower);
            books.add(Book);//装入集合中
        }
        resultSet.close();
        statement.close();
        return books;
    }
    //借书
    public static int borrowBook(String bookName) throws Exception {
        Connection connection = DBUitls.getConnection();
        String sql = "update book set status = '申请借阅中' where bookName = '" + bookName + "' and status = '在馆'";
        PreparedStatement statement = connection.prepareStatement(sql);
        //修改图书的状态为申请借阅中
        int count = statement.executeUpdate();
        statement.close();
        return count;
    }
    public static int returnBook(String bookName) throws Exception{
        Connection connection = DBUitls.getConnection();
        String sql = "update book set status = '申请归还中' where bookName = '" + bookName + "'";
        PreparedStatement statement = connection.prepareStatement(sql);
        //修改图书的状态为申请归还中
        int count = statement.executeUpdate();
        statement.close();
        return count;
    }
    public static ArrayList<book> searchBook(String bookInformation) throws Exception{
        Connection connection = DBUitls.getConnection();
        //把书籍的名字信息每个字中间都加%，便于like语句的使用
        String message = "";
        for (int i = 0; i < bookInformation.length() ; i++) {
            message += bookInformation.substring(i,i+1) + "%";
        }
        String sql = "select * from book where bookName like '%"+ message +"'";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet= statement.executeQuery();
        ArrayList<book> books = new ArrayList<>();
        //将所有对象装入集合返回
        while(resultSet.next()){
            String bookName = resultSet.getString(2);
            String writer = resultSet.getString(3);
            String kind = resultSet.getString(4);
            String number = resultSet.getString(5);
            String publish = resultSet.getString(6);
            String bookIntroduction = resultSet.getString(7);
            String writerIntroduction = resultSet.getString(8);
            String statue = resultSet.getString(9);
            String borrowNumber = resultSet.getString(10);
            String picture = resultSet.getString(11);
            String borrower = resultSet.getString(12);
            book Book = new book(bookName,writer,kind,number,publish,bookIntroduction,writerIntroduction,statue,borrowNumber,picture,borrower);
            books.add(Book);//装入集合中
        }
        resultSet.close();
        statement.close();
        return books;
    }
    public static ArrayList<record> getBorrowBookRecord() throws Exception {
        Connection connection = DBUitls.getConnection();
        String sql = "select * from record";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        ArrayList<record> records = new ArrayList<>();
        while(resultSet.next()){
            String username = resultSet.getString(2);
            String bookName = resultSet.getString(3);
            String number = resultSet.getString(4);
            String borrowTime = resultSet.getString(5);
            String returnTime = resultSet.getString(6);
            String bookId = resultSet.getString(7);
            record oneRecord = new record(username,bookName,number,borrowTime,returnTime,bookId);
            records.add(oneRecord);
        }
        resultSet.close();
        statement.close();
        return records;
    }
}
